/*Textarea prescription changes - Start*/
Alter table dbo.Appointment_Document ALTER COLUMN Appt_Image_Name nvarchar(200) NULL
Alter table dbo.Appointment_Document Add Prescription varchar(MAX) NULL
/*Textarea prescription changes - End*/

/*Report Table changes - Start*/
Alter table dbo.Patient Add  Patient_Location_Pin int NULL
Alter table dbo.Patient Add Patient_Location_Name nvarchar(50) NULL
/*Report changes - End*/

/*Report Payment table creation - Start*/

USE [HMS]
GO
/****** Object:  Table [dbo].[Payment]    Script Date: 11/30/2014 20:34:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Payment](
	[Payment_Id] [int] IDENTITY(1,1) NOT NULL,
	[Doc_Appt_Id] [int] NOT NULL,
	[Amt_Paid] [decimal](8, 2) NOT NULL,
	[Total] [decimal](8, 2) NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Payment] ON
INSERT [dbo].[Payment] ([Payment_Id], [Doc_Appt_Id], [Amt_Paid], [Total]) VALUES (1, 1, CAST(100.00 AS Decimal(8, 2)), CAST(200.00 AS Decimal(8, 2)))
INSERT [dbo].[Payment] ([Payment_Id], [Doc_Appt_Id], [Amt_Paid], [Total]) VALUES (5, 2, CAST(300.25 AS Decimal(8, 2)), CAST(500.80 AS Decimal(8, 2)))
INSERT [dbo].[Payment] ([Payment_Id], [Doc_Appt_Id], [Amt_Paid], [Total]) VALUES (6, 3, CAST(200.00 AS Decimal(8, 2)), CAST(300.00 AS Decimal(8, 2)))
INSERT [dbo].[Payment] ([Payment_Id], [Doc_Appt_Id], [Amt_Paid], [Total]) VALUES (7, 4, CAST(500.00 AS Decimal(8, 2)), CAST(700.00 AS Decimal(8, 2)))
INSERT [dbo].[Payment] ([Payment_Id], [Doc_Appt_Id], [Amt_Paid], [Total]) VALUES (8, 5, CAST(500.00 AS Decimal(8, 2)), CAST(500.00 AS Decimal(8, 2)))
SET IDENTITY_INSERT [dbo].[Payment] OFF
/****** Object:  ForeignKey [FK_Payment_Doctor_Appointment]    Script Date: 11/30/2014 20:34:10 ******/
ALTER TABLE [dbo].[Payment]  WITH CHECK ADD  CONSTRAINT [FK_Payment_Doctor_Appointment] FOREIGN KEY([Doc_Appt_Id])
REFERENCES [dbo].[Doctor_Appointment] ([Doc_Appt_Id])
GO
ALTER TABLE [dbo].[Payment] CHECK CONSTRAINT [FK_Payment_Doctor_Appointment]
GO

/*Report payment Table creation - End*/

/*Stored procedure - Start*/
/*USP_GetImagesByDocApptId - Start*/
USE [HMS]
GO

/****** Object:  StoredProcedure [dbo].[USP_GetImagesbyDocApptId]    Script Date: 11/30/2014 20:35:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<ThoughtBoat>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[USP_GetImagesbyDocApptId]
	-- Add the parameters for the stored procedure here
	@DocApptId int
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	SELECT
	AD.Appt_Document_Id,
	AD.Appt_Image_Name,
	AD.Prescription,
	AD.Created_Date
	 FROM Appointment_Document AD
	WHERE AD.Doc_Appt_Id = @DocApptId
	OR AD.Patient_Id = @DocApptId
	
END
GO
/*USP_GetImagesByDocApptId - End*/

/*USP_GetFinanceReport - Start*/
USE [HMS]
GO
/****** Object:  StoredProcedure [dbo].[USP_GetFinanceReport]    Script Date: 11/30/2014 20:53:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--exec [dbo].[USP_GetImagesbyDocApptId] 31
Create PROCEDURE [dbo].[USP_GetFinanceReport]
	-- Add the parameters for the stored procedure here
	@StartDate datetime,
	@EndDate datetime,
	@CliniId int,
	@DoctorId int,
	@LocationId int
	
AS
BEGIN
	SET NOCOUNT ON;
	--if  @DoctorId = 0 and @LocationId = 0 and @CliniId = 0
	begin	
		SELECT
		Cast(DA.Doc_Appt_Date_Time as DATE) as Appt_Date,
		p.Patient_Name,
		py.Amt_Paid,
		py.Total
		 FROM dbo.Doctor_Appointment DA
		 Join dbo.Patient p on(p.Patient_id = da.Patient_Id)
		 Join dbo.Payment py on(py.Doc_Appt_Id = Da.Doc_Appt_Id) 
		WHERE CAST(DA.Doc_Appt_Date_Time as DATE) >= CAST('2014-08-27 22:12:24.847' as DATE) 
		and CAST(DA.Doc_Appt_Date_Time as DATE)<= CAST('2014-11-27 22:12:24.847' as DATE)
		Group By Cast(DA.Doc_Appt_Date_Time as DATE), p.Patient_Name,
		py.Amt_Paid,
		py.Total
	End	
		
END

/*USP_GetFinacneReport - End*/

/*USP_GetLocationBasedPatientCount - Start */
USE [HMS]
GO
/****** Object:  StoredProcedure [dbo].[USP_GetLocationBasedPatientCount]    Script Date: 11/30/2014 20:55:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<ThoughtBoat>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
--exec [dbo].[USP_GetImagesbyDocApptId] 31
Create PROCEDURE [dbo].[USP_GetLocationBasedPatientCount]
	-- Add the parameters for the stored procedure here
	@StartDate datetime,
	@EndDate datetime,
	@CliniId int,
	@DoctorId int,
	@LocationId int
	
AS
BEGIN
	SET NOCOUNT ON;
	if  @DoctorId = 0 and @LocationId = 0 and @CliniId = 0
	begin	
		SELECT
		Count(P.Patient_id)PatientCount,
		P.Patient_Location_Name,
		P.Patient_Location_Pin
		 FROM Patient P
		WHERE CAST(P.Created_Date as DATE) >= CAST(@StartDate as DATE) 
		and CAST(P.Created_Date as DATE)<= CAST(@EndDate as DATE)
		Group By P.Patient_Location_Pin,P.Patient_Location_Name
	End	
	------------------------------------------------
	else if  @DoctorId = 0 and @LocationId = 0 and @CliniId <> 0
	begin	
		SELECT
		Count(P.Patient_id)PatientCount,
		P.Patient_Location_Name,
		P.Patient_Location_Pin
		 FROM Patient P
		WHERE CAST(P.Created_Date as DATE) >= CAST(@StartDate as DATE) 
		and CAST(P.Created_Date as DATE)<= CAST(@EndDate as DATE)
		and p.Patient_Clinic_Id = @CliniId		
		Group By P.Patient_Location_Pin,P.Patient_Location_Name
	End	
	-----------------------------------------------------
	else if @DoctorId = 0 and @LocationId <> 0 and @CliniId <> 0 
	begin
		select
		Count(P.Patient_id)PatientCount,
		P.Patient_Location_Name,
		P.Patient_Location_Pin
		FROM 		 
			Patient P 
			join Doctor_Appointment DP on (p.Patient_id = DP.Patient_Id)
			join Doctor_Location DL on(DP.Doc_Loc_Id = DL.Doc_Loc_Id)
		 
		WHERE CAST(P.Created_Date as DATE) >= CAST(@StartDate as DATE) 
		and CAST(P.Created_Date as DATE)<= CAST(@EndDate as DATE)
		and DL.Loc_Id = @LocationId
		and P.Patient_Clinic_Id = @CliniId
		Group By P.Patient_Location_Pin,P.Patient_Location_Name
	end
	------------------------------------------------------------
	else if @DoctorId <> 0 and @LocationId <> 0 and @CliniId <> 0 
	begin
		select
		Count(P.Patient_id)PatientCount,
		P.Patient_Location_Name,
		P.Patient_Location_Pin
		FROM 		 
			Patient P 
			join Doctor_Appointment DP on (p.Patient_id = DP.Patient_Id)
			join Doctor_Location DL on(DP.Doc_Loc_Id = DL.Doc_Loc_Id)
		 
		WHERE CAST(P.Created_Date as DATE) >= CAST(@StartDate as DATE) 
		and CAST(P.Created_Date as DATE)<= CAST(@EndDate as DATE)
		and DL.Doc_Id = @DoctorId
		and DL.Loc_Id = @LocationId
		and P.Patient_Clinic_Id = @CliniId
		Group By P.Patient_Location_Pin,P.Patient_Location_Name
	end
		
END

/*USP_GetLocationBasedPatientCount - End */
/*Stored procedure - End */
